package services.db;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author sergiu.marin
 * @since 26.01.2014
 */
public class ScanTablesTask {


    private static List<Result> queryOriginalDBByTableName(String table) throws SQLException {
        List<Result> results = new ArrayList<Result>();

        Connection originalDBConn = DBUtils.getConnection("localhost", "company", "root", "root");
        String tableQuery = "SELECT * FROM " + table;

        try {
            PreparedStatement statement = originalDBConn.prepareStatement(tableQuery);
            ResultSet rs = statement.executeQuery();

            while (rs.next()) {
                int rowId = rs.getInt(1);
                String value = "";
                for (int i = 2; i <= rs.getMetaData().getColumnCount(); i++) {
                    if (rs.getObject(i) != null) {
                        value += rs.getObject(i).toString();
                    }
                }
                results.add(new Result(rowId, DBUtils.md5(value)));
            }

        } finally {
            if (originalDBConn != null) {
                originalDBConn.close();
            }
        }
        return results;
    }

    public void executeTask() {
        // get connection to changes tracker data base
        Connection connection = DBUtils.getConnection("localhost", "change_tracker_db", "root", "root");
        try {
            String unreadRowQuery = "SELECT * FROM db_tables WHERE active=true AND read_time is null limit 1";
            String minDateRow     = "SELECT * FROM db_tables WHERE active=true AND read_time=(SELECT MIN(read_time)  FROM db_tables limit 1)";
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(unreadRowQuery);
            if (!rs.next()) {
                rs = statement.executeQuery(minDateRow);

                if (!rs.next()) {
                    return;
                }
            }

            int tableId = rs.getInt("id");
            String tableName = rs.getString("table_name");
            String updateReadTime = "UPDATE db_tables SET read_time=NOW() WHERE id=?";
            PreparedStatement preparedStatement = connection.prepareStatement(updateReadTime);
            preparedStatement.setInt(1, tableId);
            preparedStatement.executeUpdate();

            List<Result> results = queryOriginalDBByTableName(tableName);

            updateReadTime = "SELECT * FROM table_row_changes WHERE table_id=?";
            preparedStatement = connection.prepareStatement(updateReadTime);
            preparedStatement.setInt(1, tableId);
            rs = preparedStatement.executeQuery();

            Map<Integer, Result> tableChangedRowsMap = new HashMap<Integer, Result>();
            while (rs.next()) {
                tableChangedRowsMap.put(rs.getInt("row_id"), new Result(rs.getInt("id"), rs.getString("control_value")));
            }

            for (Result result : results) {
                Result r = tableChangedRowsMap.get(result.getId());
                if (r == null) {

                    updateReadTime = "INSERT INTO table_row_changes(control_value, changed, table_id, row_id) VALUES (?, true, ?, ?)";
                    preparedStatement = connection.prepareStatement(updateReadTime);
                    preparedStatement.setString(1, result.getControlValue());
                    preparedStatement.setInt(2, tableId);
                    preparedStatement.setInt(3, result.getId());
                    //preparedStatement.setString(4, result.getPrimaryCol());
                    preparedStatement.executeUpdate();

                } else if (!result.getControlValue().equals(r.getControlValue())) {

                    updateReadTime = "UPDATE table_row_changes SET control_value=?, changed=true WHERE id=?";
                    preparedStatement = connection.prepareStatement(updateReadTime);
                    preparedStatement.setString(1, result.getControlValue());
                    preparedStatement.setInt(2, r.getId());
                    preparedStatement.executeUpdate();

                }
            }
        } catch (SQLException e) {
            play.Logger.debug("An error has occurred: " + e.getMessage());
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    play.Logger.debug("An error has occurred: " + e.getMessage());
                }
            }
        }
    }

}
